from typing import Union

from sqlalchemy import update, CursorResult, text, asc, func
from sqlmodel import select, desc, col
from sqlmodel.sql._expression_select_cls import SelectOfScalar, Select

from src.base.beans import PageResult
from src.config.db import mysqlClient
from src.domain.sys_post import SysPost, SysPostReq


def get_post_by_id(post_id: int) -> SysPost:
    return mysqlClient.exec(select(SysPost).where(SysPost.post_id == post_id).where(SysPost.del_flag == False)).first()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysPost = None):
    stmt.where(SysPost.del_flag == False)
    if entity:
        if entity.post_id:
            stmt = stmt.where(SysPost.post_id == entity.post_id)
        if entity.post_name:
            stmt = stmt.where(SysPost.post_name == entity.post_name)
        if entity.status:
            stmt = stmt.where(SysPost.status == entity.status)
    return stmt


def get_post_list_by_condition(entity: SysPost = None) -> list[SysPost]:
    stmt = select(SysPost)
    stmt = where_handle(stmt, entity)
    # order_by() 是返回一个新的语句对象，而不是原地修改
    stmt = stmt.order_by(desc(SysPost.sort))
    users = mysqlClient.exec(stmt)
    return users


def get_post_page_by_condition(entity: SysPostReq = None) -> PageResult:
    search = SysPost(**entity.model_dump())
    list_stmt = select(SysPost)
    list_stmt = where_handle(list_stmt, search)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysPost.post_name))
    list_stmt = list_stmt.limit(entity.size).offset(entity.size * (entity.current - 1))
    users = mysqlClient.exec(list_stmt).all()

    total_stmt = select(func.count(SysPost.user_id))
    total_stmt = where_handle(total_stmt, search)
    total_result = mysqlClient.exec(total_stmt)
    total = total_result.one_or_none()
    return PageResult(total=total, data=users, current=entity.current, size=entity.size)


def insert_post(entity: SysPost = None):
    mysqlClient.add(entity)


def update_post(entity: SysPost = None) -> int:
    stmt = update(SysPost).values(entity.model_dump()).where(col(SysPost.post_id).is_(entity.post_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_post(post_id: int = None) -> int:
    stmt = update(SysPost).values(SysPost(del_flag=True).model_dump()).where(col(SysPost.post_id).is_(post_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_posts(post_ids: list[int] = None) -> int:
    stmt = update(SysPost).values(SysPost(del_flag=True).model_dump()).where(col(SysPost.post_id).in_(post_ids))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()
